﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace BusinessLayer
{
    public class Book
    {
        private string _title, _isbn, _publicationYear, _publisher, _author, _barcode, _status, _signum, _aid;
        private int _signid, _libNo;

        public string Title { get { return this._title; } set { this._title = value; } }
        public string ISBN { get { return this._isbn; } set { this._isbn = value; } }
        public int SignId { get { return this._signid; } set { this._signid = value; } }
        public string PublicationYear { get { return this._publicationYear; } set { this._publicationYear = value; } }
        public string Publisher { get { return this._publisher; } set { this._publisher = value; } }
        public int LibNo { get { return this._libNo; } set { this._libNo = value; } }
        public string Author { get { return this._author; } set { this._author = value; } }
        public string Barcode { get { return this._barcode; } set { this._barcode = value; } }
        public string Status { get { return this._status; } set { this._status = value; } }
        public string Signum { get { return this._signum; } set { this._signum = value; } }
        public string Aid { get { return this._aid; } set { this._aid = value; } }





        public Book() { }

        public static Book getByISBNAdmin(string ISBN) {
            string sql = "SELECT * FROM BOOK INNER JOIN BOOK_AUTHOR ON BOOK.ISBN=BOOK_AUTHOR.ISBN WHERE BOOK.ISBN=@ISBN";
            Book newBook = new Book();
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.Add(new SqlParameter("ISBN", ISBN));

            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                if (dar.Read())
                {
                    newBook.Title = dar["Title"] as string;
                    newBook.ISBN = dar["ISBN"] as string;
                    newBook.PublicationYear = dar["PublicationYear"] as string;
                    newBook.Publisher = dar["Publisher"].ToString();
                    newBook.SignId = (dar["SignId"] == DBNull.Value) ? 0 : (int)dar["SignId"];
                    newBook.LibNo = (dar["LibNo"] == DBNull.Value) ? 0 : (int)dar["LibNo"];                    
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }

            return newBook;
        }

        public static List<Book> searchByISBNorTitle(string val, string term)
        {
            term = "%" + term + "%";
            term = term.Replace(" ", "%");
            string sql ="SELECT Title, ISBN, PublicationYear, Publisher, LibNo, Signum , STUFF((SELECT ', ' + A.AuthorName FROM (SELECT CASE WHEN AUTHOR.BirthYear IS NULL THEN AUTHOR.FirstName + ' ' + AUTHOR.LastName ELSE AUTHOR.FirstName + ' ' + AUTHOR.LastName + ' (' + AUTHOR.BirthYear + ')' END as AuthorName, AUTHOR.Aid, BOOK.*,  CLASSIFICATION.Signum " +
                "FROM AUTHOR INNER JOIN BOOK_AUTHOR ON AUTHOR.Aid = BOOK_AUTHOR.Aid INNER JOIN BOOK ON BOOK_AUTHOR.ISBN = BOOK.ISBN INNER JOIN CLASSIFICATION ON BOOK.SignId = CLASSIFICATION.SignId WHERE BOOK." + val + " LIKE @Term) A WHERE A.Title = B.Title  FOR XML PATH('')),1,1,'') AS AuthorNames " +
                "FROM (SELECT CASE WHEN AUTHOR.BirthYear IS NULL THEN AUTHOR.FirstName + ' ' + AUTHOR.LastName ELSE AUTHOR.FirstName + ' ' + AUTHOR.LastName + ' (' + AUTHOR.BirthYear + ')' END as AuthorName, AUTHOR.Aid, BOOK.*,  CLASSIFICATION.Signum "+
                "FROM AUTHOR INNER JOIN BOOK_AUTHOR ON AUTHOR.Aid = BOOK_AUTHOR.Aid INNER JOIN BOOK ON BOOK_AUTHOR.ISBN = BOOK.ISBN INNER JOIN CLASSIFICATION ON BOOK.SignId = CLASSIFICATION.SignId  WHERE BOOK." + val + " LIKE @Term) B " +
                "GROUP BY Title, ISBN, PublicationYear, Publisher, LibNo, Signum";

            List<Book> results = new List<Book>();
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.Add(new SqlParameter("Term", term));

            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    Book newBook = new Book();
                    newBook.Title = dar["Title"] as string;
                    newBook.ISBN = dar["ISBN"] as string;
                    newBook.PublicationYear = dar["PublicationYear"] as string;
                    newBook.Publisher = dar["Publisher"] as string;
                    //newBook.SignId = (dar["SignId"] == DBNull.Value) ? 0 : (int)dar["SignId"];
                    newBook.LibNo = (dar["LibNo"] == DBNull.Value) ? 0 : (int)dar["LibNo"];
                    newBook.Author = dar["AuthorNames"] as string;                    
                    newBook.Signum = dar["Signum"] as string;
                    results.Add(newBook);
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }

            return results;
        }


        public static List<Book> getByISBNorTitle(string val, string term)
        {
            term = "%" + term + "%";
            term = term.Replace(" ", "%");
            string sql = 
                "SELECT Title, ISBN, PublicationYear, Publisher, LibNo, Barcode, status, Signum , STUFF((SELECT ', ' + A.AuthorName FROM (SELECT CASE WHEN AUTHOR.BirthYear IS NULL THEN AUTHOR.FirstName + ' ' + AUTHOR.LastName ELSE AUTHOR.FirstName + ' ' + AUTHOR.LastName + ' (' + AUTHOR.BirthYear + ')' END as AuthorName, AUTHOR.Aid, BOOK.*, COPY.Barcode, STATUS.status, CLASSIFICATION.Signum " +
                "FROM AUTHOR INNER JOIN BOOK_AUTHOR ON AUTHOR.Aid = BOOK_AUTHOR.Aid INNER JOIN BOOK ON BOOK_AUTHOR.ISBN = BOOK.ISBN INNER JOIN COPY ON COPY.ISBN = BOOK.ISBN INNER JOIN CLASSIFICATION ON BOOK.SignId = CLASSIFICATION.SignId INNER JOIN STATUS ON COPY.StatusId = STATUS.statusid WHERE BOOK."+ val +" LIKE @Term) A WHERE A.Barcode = B.Barcode AND A.Title = B.Title  FOR XML PATH('')),1,1,'') AS AuthorNames " +
                "FROM (SELECT CASE WHEN AUTHOR.BirthYear IS NULL THEN AUTHOR.FirstName + ' ' + AUTHOR.LastName ELSE AUTHOR.FirstName + ' ' + AUTHOR.LastName + ' (' + AUTHOR.BirthYear + ')' END as AuthorName, AUTHOR.Aid, BOOK.*, COPY.Barcode, STATUS.status, CLASSIFICATION.Signum " +
                "FROM AUTHOR INNER JOIN BOOK_AUTHOR ON AUTHOR.Aid = BOOK_AUTHOR.Aid INNER JOIN BOOK ON BOOK_AUTHOR.ISBN = BOOK.ISBN INNER JOIN CLASSIFICATION ON BOOK.SignId = CLASSIFICATION.SignId INNER JOIN COPY ON COPY.ISBN = BOOK.ISBN INNER JOIN STATUS ON COPY.StatusId = STATUS.statusid WHERE BOOK." + val + " LIKE @Term) B " +
                "GROUP BY Title, ISBN, PublicationYear, Publisher, LibNo, Barcode, Status, Signum";

            List<Book> results = new List<Book>();
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.Add(new SqlParameter("Term", term));

            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    Book newBook = new Book();
                    newBook.Title = dar["Title"] as string;
                    newBook.ISBN = dar["ISBN"] as string;
                    newBook.PublicationYear = dar["PublicationYear"] as string;
                    newBook.Publisher = dar["Publisher"] as string;
                    //newBook.SignId = (dar["SignId"] == DBNull.Value) ? 0 : (int)dar["SignId"];
                    newBook.LibNo = (dar["LibNo"] == DBNull.Value) ? 0 : (int)dar["LibNo"];
                    newBook.Author = dar["AuthorNames"] as string;
                    newBook.Barcode = dar["Barcode"] as string;
                    newBook.Status = dar["status"] as string;
                    newBook.Signum = dar["Signum"] as string;
                    results.Add(newBook);
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }

            return results;
        }

        public static List<Book> getByAuthorName(string name)
        {
            string sql =
                "SELECT Title, ISBN, LibNo, Barcode, status, Signum, AuthorNames " +
                "FROM (SELECT Title, ISBN, PublicationYear, Publisher, SignId, LibNo, Barcode, status, Signum , STUFF((SELECT ', ' + A.AuthorName FROM (SELECT AUTHOR.FirstName + ' ' + AUTHOR.LastName AS AuthorName, AUTHOR.Aid, BOOK.*, COPY.Barcode, STATUS.status, CLASSIFICATION.Signum " +
                "FROM AUTHOR INNER JOIN BOOK_AUTHOR ON AUTHOR.Aid = BOOK_AUTHOR.Aid INNER JOIN BOOK ON BOOK_AUTHOR.ISBN = BOOK.ISBN INNER JOIN COPY ON COPY.ISBN = BOOK.ISBN INNER JOIN CLASSIFICATION ON BOOK.SignId = CLASSIFICATION.SignId INNER JOIN STATUS ON COPY.StatusId = STATUS.statusid) A WHERE A.Barcode = B.Barcode AND A.Title = B.Title  FOR XML PATH('')),1,1,'') AS AuthorNames " +
                "FROM (SELECT AUTHOR.FirstName + ' ' + AUTHOR.LastName AS AuthorName, AUTHOR.Aid, BOOK.*, COPY.Barcode, STATUS.status, CLASSIFICATION.Signum " +
                "FROM AUTHOR INNER JOIN BOOK_AUTHOR ON AUTHOR.Aid = BOOK_AUTHOR.Aid INNER JOIN BOOK ON BOOK_AUTHOR.ISBN = BOOK.ISBN INNER JOIN CLASSIFICATION ON BOOK.SignId = CLASSIFICATION.SignId INNER JOIN COPY ON COPY.ISBN = BOOK.ISBN INNER JOIN STATUS ON COPY.StatusId = STATUS.statusid) B " +
                "GROUP BY Title, ISBN, PublicationYear, Publisher, SignId, LibNo, Barcode, status, Signum, AuthorName) " +
                "AS test WHERE AuthorNames LIKE '%@Name%' ";

            List<Book> results = new List<Book>();
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.Add(new SqlParameter("Name", name));

            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    Book newBook = new Book();
                    newBook.Title = dar["Title"] as string;
                    newBook.ISBN = dar["ISBN"] as string;
                    newBook.LibNo = (dar["LibNo"] == DBNull.Value) ? 0 : (int)dar["LibNo"];
                    newBook.Author = dar["AuthorNames"] as string;
                    newBook.Barcode = dar["Barcode"] as string;
                    newBook.Status = dar["status"] as string;
                    newBook.Signum = dar["Signum"] as string;
                    results.Add(newBook);
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }

            return results;
        }

        public static List<Book> getAll()
        {
            string sql = "SELECT Title, ISBN, PublicationYear, Publisher, SignId, LibNo , STUFF((SELECT ', ' + A.AuthorName FROM (SELECT CASE WHEN AUTHOR.BirthYear IS NULL THEN AUTHOR.FirstName + ' ' + AUTHOR.LastName ELSE AUTHOR.FirstName + ' ' + AUTHOR.LastName + ' (' + AUTHOR.BirthYear + ')' END as AuthorName, AUTHOR.Aid, BOOK.* " +
                "FROM AUTHOR INNER JOIN BOOK_AUTHOR ON AUTHOR.Aid = BOOK_AUTHOR.Aid INNER JOIN BOOK ON BOOK_AUTHOR.ISBN = BOOK.ISBN) A WHERE A.Title = B.Title FOR XML PATH('')),1,1,'') AS AuthorNames " +
                "FROM (SELECT CASE WHEN AUTHOR.BirthYear IS NULL THEN AUTHOR.FirstName + ' ' + AUTHOR.LastName ELSE AUTHOR.FirstName + ' ' + AUTHOR.LastName + ' (' + AUTHOR.BirthYear + ')' END as AuthorName, AUTHOR.Aid, BOOK.* " +
                "FROM AUTHOR INNER JOIN BOOK_AUTHOR ON AUTHOR.Aid = BOOK_AUTHOR.Aid INNER JOIN BOOK ON BOOK_AUTHOR.ISBN = BOOK.ISBN) B " +
                "GROUP BY Title, ISBN, PublicationYear, Publisher, SignId, LibNo";
            List<Book> results = new List<Book>();
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    Book newBook = new Book();
                    newBook.Title = dar["Title"] as string;
                    newBook.ISBN = dar["ISBN"] as string;
                    newBook.PublicationYear = dar["PublicationYear"] as string;
                    newBook.Publisher = dar["Publisher"] as string;
                    newBook.SignId = (dar["SignId"] == DBNull.Value) ? 0 : (int)dar["SignId"];
                    newBook.LibNo = (dar["LibNo"] == DBNull.Value) ? 0 : (int)dar["LibNo"];
                    newBook.Author = dar["AuthorNames"] as string;
                    results.Add(newBook);
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }
            return results;
        }


        public static List<Book> getByAuthor(int authorID)
        {
            string SQL = "SELECT BOOK.* FROM BOOK " +
                           "INNER JOIN BOOK_AUTHOR ON BOOK_AUTHOR.ISBN=BOOK.ISBN " +
                           "WHERE BOOK_AUTHOR.Aid=@Aid";
            List<Book> results = new List<Book>();
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand(SQL, con);
            cmd.Parameters.Add(new SqlParameter("Aid", authorID));
            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    Book book = new Book();
                    book._title = dar["Title"] as string;
                    book._isbn = dar["ISBN"] as string;
                    results.Add(book);
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }

            return results;
        }

        public static void SaveBook(string ISBN, string Title, int SignId, string PublicationYear, string Publisher, int LibNo, string Aid)
        {
            Random rand = new Random();
            string val = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString();
            SqlConnection dbCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand dbCmd = new SqlCommand("INSERT INTO dbo.BOOK (ISBN,Title,SignId,PublicationYear,Publisher,LibNo) VALUES (@ISBN,@Title,@SignId,@PublicationYear, @Publisher ,@LibNo); INSERT INTO dbo.COPY(Barcode, ISBN, StatusId) VALUES(@Barcode, @ISBN, @StatusId)", dbCon);

            dbCmd.Parameters.Add(new SqlParameter("ISBN", ISBN));
            dbCmd.Parameters.Add(new SqlParameter("Title", Title));
            dbCmd.Parameters.Add(new SqlParameter("SignId", SignId));
            dbCmd.Parameters.Add(new SqlParameter("PublicationYear", PublicationYear));
            dbCmd.Parameters.Add(new SqlParameter("Barcode", Convert.ToInt64(val)));
            dbCmd.Parameters.Add(new SqlParameter("StatusId", 1));

            if (Publisher == "" || Publisher == "null" || Publisher == " " || Publisher == "None" || Publisher == "Ingen")
            {
                dbCmd.Parameters.Add(new SqlParameter("Publisher", DBNull.Value));
            }
            else
            {
                dbCmd.Parameters.Add(new SqlParameter("Publisher", Publisher));
            }
            dbCmd.Parameters.Add(new SqlParameter("LibNo", LibNo));

            try
            {
                dbCon.Open();
                dbCmd.ExecuteReader();
            }
            catch (Exception Exc) { throw Exc; }
            finally
            {
                dbCon.Close();

                string Cmd = "INSERT INTO dbo.BOOK_AUTHOR (ISBN,Aid) VALUES ";
                string[] AidList = Aid.Split(',');
                int number;
                int count = 1;
                foreach( string id in AidList)
                {
                    if (Int32.TryParse(id, out number))
                    {
                        if (count == AidList.Count())
                        {
                            Cmd += "(" + ISBN + ","+number+")";
                        }
                        else
                        {
                            count++;
                            Cmd += "("+ISBN+","+number+"),";
                        }
                    }
                
                }
                SqlConnection dbCon2 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
                SqlCommand dbCmd2 = new SqlCommand(Cmd, dbCon2);
                try
                {
                    dbCon2.Open();
                    dbCmd2.ExecuteReader();
                }
                catch (Exception Exc) { throw Exc; }
                finally
                {
                    dbCon2.Close();
                }
            
            }

        }

        public static void SaveCopy(string Barcode, string Location, int statusId, string ISBN)
        {
            SqlConnection dbCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);

            SqlCommand dbCmd = new SqlCommand("INSERT INTO dbo.COPY (Barcode,Location,statusId,ISBN) VALUES (@Barcode,@Location,@statusId,@ISBN)", dbCon);

            dbCmd.Parameters.Add(new SqlParameter("Barcode", Barcode));
            dbCmd.Parameters.Add(new SqlParameter("Location", Location));
            dbCmd.Parameters.Add(new SqlParameter("statusId", statusId));
            dbCmd.Parameters.Add(new SqlParameter("ISBN", ISBN));

            try
            {
                dbCon.Open();
                dbCmd.ExecuteReader();
            }
            catch (Exception Exc) { throw Exc; }
            finally { dbCon.Close(); }
        }

        public static void ModBook(string origISBN, string ISBN, string Title, int SignId, string PublicationYear, string Publisher, int LibNo)
        {
            string sql = "UPDATE dbo.book SET Title=@Title, SignId=@SignId, PublicationYear=@PublicationYear, Publisher=@Publisher ,LibNo=@LibNo, ISBN=@ISBN WHERE ISBN=@origISBN";

            SqlConnection dbCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand dbCmd = new SqlCommand(sql, dbCon);
            dbCmd.Parameters.Add(new SqlParameter("Title", Title));
            dbCmd.Parameters.Add(new SqlParameter("SignId", SignId));
            dbCmd.Parameters.Add(new SqlParameter("PublicationYear", PublicationYear));

            if (Publisher == "" || Publisher == "null" || Publisher == " " || Publisher == "None" || Publisher == "Ingen")
            {
                dbCmd.Parameters.Add(new SqlParameter("Publisher", DBNull.Value));
            }
            else
            {
                dbCmd.Parameters.Add(new SqlParameter("Publisher", Publisher));
            }
            dbCmd.Parameters.Add(new SqlParameter("LibNo", LibNo));
            dbCmd.Parameters.Add(new SqlParameter("ISBN", ISBN));
            dbCmd.Parameters.Add(new SqlParameter("origISBN", origISBN));

            try
            {
                dbCon.Open();
                dbCmd.ExecuteReader();
            }
            catch (Exception Exc) { throw Exc; }
            finally { dbCon.Close(); }

        }

        public static void DeleteBook(string ISBN)
        {

            string sql = "DELETE FROM BOOK_AUTHOR WHERE BOOK_AUTHOR.ISBN = @ISBN; DELETE FROM COPY WHERE COPY.ISBN=@ISBN; DELETE FROM  BOOK WHERE BOOK.ISBN=@ISBN";
            SqlConnection dbCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand dbCmd = new SqlCommand(sql, dbCon);
            dbCmd.Parameters.Add("ISBN", ISBN);
            try
            {
                dbCon.Open();
                dbCmd.ExecuteReader();
            }
            catch (Exception Exc) { throw Exc; }
            finally { dbCon.Close(); }
        }
    }
}